import os
import logging
import sys
import string

log = logging.getLogger('db')

DB_backend = None                       # XXX/bowei -- figure out some
                                        # other way to do this other
                                        # than a global variable

# ----------------------------------------------------------------------------
class Db:
    """Consolidates all DB functions into a single class for
    abstraction later."""

    def __init__(self, type, connection):
        self.type_ = type
        self.conn_ = connection
        
        self.schemas_ = {}
        self.refresh_schemas()
        
    def cursor(self):
        return self.conn_.cursor()

    def query(self, *query):
        """Syntactic sugar for doing a query and fetchall()"""
        c = self.conn_.cursor()
        c.execute(*query)
        return c.fetchall()

    def exec_sql(self, *query):
        """Syntactic sugar for doing a query with no fetch"""
        c = self.conn_.cursor()
        c.execute(*query)

    def refresh_schemas(self):
        """Refresh the cached information about the database schema"""
        c = self.cursor()
        for t in self.table_names():
            log.debug('Getting schema for %s' % t)
            c.execute('SELECT * FROM %s LIMIT 0' % t)
            c.fetchall()
            self.schemas_[t] = [d[0] for d in c.description]
    
    def escape_str(self, s):
        return self.conn_.escape_string(s)
    
#    def description(self):
#        return self.cursor_.description
#
#    def rows_count(self):
#       return self.cursor_.row_count

    def commit(self):
        log.debug('---- COMMIT ----')
        self.conn_.commit()

    def placeholder_str(self):
        """This is the placeholder used to denote values to fill in."""
        raise NotImplementedError()

    def table_schema(self, table_name):
        # XXX/bowei -- include more information other than the column names
        """@return A list of the columns, in the order they appear in
        the table."""
        return self.schemas_[table_name]

    def table_exists(self, table_name):
        return table_name in self.schemas_

    def table_names(self):
        raise NotImplementedError()        

# ----------------------------------------------------------------------------
class MySQL(Db):
    def __init__(self, host, user, passwd, dbname, initdb):
	import MySQLdb
        global DB_backend
        
        DB_backend = MySQLdb
        
        self.dbopts_ = {'host' : host,
                        'user' : user,
                        'password' : passwd,
                        'dbname' : dbname}
        
	connection = DB_backend.connect(host, user, passwd, dbname)
	Db.__init__(self, 'mysql', connection)

    def placeholder_str(self):
        return '%s'

    def table_names(self):
        c = self.cursor()
        c.execute('SHOW TABLES;')
        return [t[0] for t in c.fetchall()]
        
# ----------------------------------------------------------------------------
class Sqlite(Db):
    def __init__(self, dbfile, initdb):
	from pysqlite2 import dbapi2 as sqlite
        global DB_backend
        DB_backend = sqlite
        
        if initdb and os.path.exists(dbfile):
            log.info('Clobbering old database "%s"' % dbfile)
            os.remove(dbfile)
        elif not initdb and not os.path.exists(dbfile):
            log.error('Database "%s" not found. You need to run with initialize first.' % dbfile)
            sys.exit(1)
        else:
            log.info('Using database file "%s"' % dbfile)
	try:
	    connection = DB_backend.connect(dbfile)
	except:
	    log.error("Can't create database file " + dbfile)
	    sys.exit(1)
	Db.__init__(self, 'sqlite', connection)

    def placeholder_str(self):
        return '?'

    def table_names(self):
        c = self.cursor()
        c.execute('SELECT name from SQLite_Master')
        return [r[0] for r in c.fetchall()]

    def escape_str(self, s):
        # XXX/bowei -- somehyow this function doesn't exist??
        return s
